﻿namespace MonetDbDataProviderTest
{
    using System;
    using System.Data;

    using Microsoft.SqlServer.Server;

    using MonetDbDataProvider;

    using NUnit.Framework;

    [TestFixture]
    public class ParameterTests : BaseTest
    {
        [SetUp]
        public void Setup()
        {
            var sql = 
                "CREATE TABLE test.TestTable (stringfield varchar(200), datefield date, timestampfield timestamp, timefield time, decimalfield decimal, tinyintfield tinyint, smallintfield smallint, intfield int, bigintfield bigint, realfield real, floatfield float, doublefield double, booleanfield boolean);";
        
            //base.RunSQL(sql);
        }

        [TearDown]
        public void Teardown()
        {
            //var sql = "DROP TABLE test.TestTable;";
            var sql = "DELETE FROM test.TestTable;";
            base.RunSQL(sql);
        }

        [TestCase("my \"\" value")]
        [TestCase("my ` value")]
        [TestCase("my Ž value")]
        [TestCase("my \\ value")]
        [TestCase("my friend's value")]
        [TestCase("my \"friend's\" value")]
        public void QuoteTest(string testValue)
        {
            // Arrange
            var cmd = new MonetDbCommand(base.MonetDbConn);
            cmd.Parameters.Add(new MonetDbParameter());

            cmd.CommandText = "INSERT INTO test.TestTable (stringfield) VALUES (@param)";

            // Act
            cmd.Connection.Open();

            ((MonetDbParameter)cmd.Parameters[0]).ParameterName = "@param";
            ((MonetDbParameter)cmd.Parameters[0]).Value = testValue;
            cmd.ExecuteNonQuery();   
            
            // Assert
            cmd.CommandText = "SELECT stringfield FROM test.TestTable;";

            var reader = cmd.ExecuteReader();

            try
            {
                reader.Read();

                Assert.AreEqual(testValue, reader.GetString(0));
            }
            catch (Exception ex)
            {
                Assert.Fail(ex.Message);
            }
            finally
            {
                cmd.Connection.Close();                
            }
        }

        [TestCase("! my value")]
        [TestCase("% my correct # value")]
        [TestCase("# my value")]
        [TestCase("[my value]")]
        [TestCase("my \t value")]
        [TestCase("my    value")]
        public void ControlCharacterTest(string testValue)
        {
            // Arrange
            var cmd = new MonetDbCommand(base.MonetDbConn);
            cmd.Parameters.Add(new MonetDbParameter());

            cmd.CommandText = "INSERT INTO test.TestTable (stringfield) VALUES (@param)";

            // Act
            cmd.Connection.Open();

            ((MonetDbParameter)cmd.Parameters[0]).ParameterName = "@param";
            ((MonetDbParameter)cmd.Parameters[0]).Value = testValue;
            cmd.ExecuteNonQuery();

            // Assert
            cmd.CommandText = "SELECT stringfield FROM test.TestTable;";

            var reader = cmd.ExecuteReader();

            try
            {
                reader.Read();

                Assert.AreEqual(testValue, reader.GetString(0));
            }
            catch (Exception ex)
            {
                Assert.Fail(ex.Message);
            }
            finally
            {
                cmd.Connection.Close();
            }
        }

        [Test]
        public void DateTimeTest()
        {
            // Arrange
            var testDate = new DateTime(2013, 01, 15);

            var cmd = new MonetDbCommand(base.MonetDbConn);
            cmd.Parameters.Add(new MonetDbParameter());

            cmd.CommandText = "INSERT INTO test.TestTable (datefield) VALUES (@param)";

            // Act
            cmd.Connection.Open();

            ((MonetDbParameter)cmd.Parameters[0]).ParameterName = "@param";
            ((MonetDbParameter)cmd.Parameters[0]).Value = testDate;
            cmd.ExecuteNonQuery();

            // Assert
            cmd.CommandText = "SELECT datefield FROM test.TestTable;";

            var reader = cmd.ExecuteReader();

            try
            {
                reader.Read();

                Assert.AreEqual(testDate, reader.GetDateTime(0));
            }
            catch (Exception ex)
            {
                Assert.Fail(ex.Message);
            }
            finally
            {
                cmd.Connection.Close();
            }
        }

        [Test]
        public void TimestampTest()
        {
            // Arrange
            var testDate = new DateTime(2013, 01, 15, 1, 10, 10);

            var cmd = new MonetDbCommand(base.MonetDbConn);
            cmd.Parameters.Add(new MonetDbParameter());

            cmd.CommandText = "INSERT INTO test.TestTable (timestampfield) VALUES (@param)";

            // Act
            cmd.Connection.Open();

            ((MonetDbParameter)cmd.Parameters[0]).ParameterName = "@param";
            ((MonetDbParameter)cmd.Parameters[0]).Value = testDate;
            cmd.ExecuteNonQuery();

            // Assert
            cmd.CommandText = "SELECT timestampfield FROM test.TestTable;";

            var reader = cmd.ExecuteReader();

            try
            {
                reader.Read();

                Assert.AreEqual(testDate, reader.GetDateTime(0));
            }
            catch (Exception ex)
            {
                Assert.Fail(ex.Message);
            }
            finally
            {
                cmd.Connection.Close();
            }
        }

        [Test]
        public void NullInsertTest()
        {
            // Arrange
            var cmd = new MonetDbCommand(base.MonetDbConn);
            cmd.Parameters.Add(new MonetDbParameter());

            cmd.CommandText = "INSERT INTO test.TestTable (stringfield) VALUES (@param)";

            // Act
            cmd.Connection.Open();

            ((MonetDbParameter)cmd.Parameters[0]).ParameterName = "@param";
            ((MonetDbParameter)cmd.Parameters[0]).Value = null;
            cmd.ExecuteNonQuery();

            // Assert
            cmd.CommandText = "SELECT stringfield FROM test.TestTable;";

            var reader = cmd.ExecuteReader();

            try
            {
                reader.Read();

                Assert.AreEqual(DBNull.Value, reader.GetValue(0));
            }
            catch (Exception ex)
            {
                Assert.Fail(ex.Message);
            }
            finally
            {
                cmd.Connection.Close();
            }
        }

        [Test]
        public void NullParameterNameTest()
        {
            // Arrange
            var cmd = new MonetDbCommand(base.MonetDbConn);

            // Act & Assert Exception
            Assert.Throws<ArgumentNullException>(() => cmd.Parameters.Add(null));
        }

        [Test]
        public void DuplicateParameterNameTest()
        {
            // Arrange
            var cmd = new MonetDbCommand(base.MonetDbConn);
            
            var param1 = new MonetDbParameter();
            param1.ParameterName = "@test";

            var param2 = new MonetDbParameter();
            param2.ParameterName = "@test";

            cmd.Parameters.Add(param1);

            // Act & Assert Exception
            Assert.Throws<ArgumentException>(() => cmd.Parameters.Add(param2));
        }

        [Test]
        public void ParameterInvalidTypeTest()
        {
            // Arrange
            var cmd = new MonetDbCommand(base.MonetDbConn);
            var param = new MonetDbParameter();
            param.Value = "this is a test value";
            param.DbType = DbType.Int32;

            // Act & Assert Exception
            Assert.Throws<FormatException>(() => cmd.Parameters.Add(param));
        }

        [Test]
        public void ParameterInvalidSizeTest()
        {
            // Arrange
            var cmd = new MonetDbCommand(base.MonetDbConn);
            var param = new MonetDbParameter();
            param.Value = "this is a test value";
            param.DbType = DbType.String;
            param.Size = 2;

            // Act & Assert Exception
            Assert.Throws<ArgumentException>(() => cmd.Parameters.Add(param));
        }

        [TestCase(55)]
        [TestCase(-33)]
        public void SignedIntTest(int testValue)
        {
            // Arrange
            var cmd = new MonetDbCommand(base.MonetDbConn);
            cmd.Parameters.Add(new MonetDbParameter());

            cmd.CommandText = "INSERT INTO test.TestTable (intfield) VALUES (@param)";

            // Act
            cmd.Connection.Open();

            ((MonetDbParameter)cmd.Parameters[0]).ParameterName = "@param";
            ((MonetDbParameter)cmd.Parameters[0]).Value = testValue;
            cmd.ExecuteNonQuery();

            // Assert
            cmd.CommandText = "SELECT intfield FROM test.TestTable;";

            var reader = cmd.ExecuteReader();

            try
            {
                reader.Read();

                Assert.AreEqual(testValue, reader.GetInt32(0));
            }
            catch (Exception ex)
            {
                Assert.Fail(ex.Message);
            }
            finally
            {
                cmd.Connection.Close();
            }
        }
    }
}
